#services/customers.py
import logging
import re
from typing import Optional, List, Dict
import pandas as pd
from sqlalchemy.orm import Session
import json
from datetime import datetime

from db.models.users import UserResponse

from db.mysql.customers import Customers
from db.mysql.dao.customers import CustomersDAO
from db.mysql.users import Users
from db.pydantic.request.call_task import CallTasksCreateRequest
from services.call_task_service import get_call_task_service
from services.redis_service import AsyncRedisService
from utils.result import Result
from core.database import get_db

logger = logging.getLogger("app." + __name__)

COLUMN_PATTERNS = {
    "customer_name": ["客户姓名", "姓名", "客户名"],
    "gender": ["性别"],
    "phone": ["手机号", "手机", "电话"],
    "age": ["年龄"],
    "city": ["所在城市", "城市"],
    "tags": ["标签"],
    "source": ["来源（小类名称）", "来源类型"],
    "type": ["客户类型"],
    "customer_level": ["客户星级"],
    "expected_amount": ["期望金额"],
    "customer_situation": ["客户情况"],
    "company_name": ["企业名称", "公司名"]
}


def find_matching_column(column_name: str, patterns: Dict[str, List[str]]) -> Optional[str]:
    """
    根据列名模式匹配找到对应的数据库字段
    """
    column_name = str(column_name).strip()

    for db_field, patterns_list in patterns.items():
        for pattern in patterns_list:
            # 使用正则表达式进行模糊匹配
            if re.search(pattern, column_name,re.IGNORECASE):
                return db_field
    return None


async def import_customers_from_excel(file_path: str, current_user: Users, db: Session = None) -> Result:
    """
    从Excel文件导入客户信息（根据实际数据格式调整）

    Args:
        file_path: Excel文件路径
        db: 数据库会话

    Returns:
        Result: 导入结果
    """
    try:
        # 读取Excel文件
        df = pd.read_excel(file_path)
        # 获取实际列名
        actual_columns = df.columns.tolist()
        # 创建从实际列名到数据库字段的映射
        column_mapping = {}
        for col in actual_columns:
            db_field = find_matching_column(col, COLUMN_PATTERNS)
            if db_field:
                column_mapping[col] = db_field
                print(f"映射列: {col} -> {db_field}")

        #检查必要的列是否存在
        required_fields = ["customer_name", "phone"]
        missing_fields = [field for field in required_fields if field not in column_mapping.values()]

        if missing_fields:
            return Result.fail(msg=f"缺少必要列: {', '.join(required_fields)}")

        # 重命名列
        df.rename(columns=column_mapping, inplace=True)
        # 只保留有映射的列
        df = df[list(column_mapping.values())]
        # 处理数据
        df = df.astype(object)
        df = df.where(pd.notnull(df), None)

        cust_dao = CustomersDAO(db=db)
        # 转换数据为字典列表
        customers_data = []
        processed_phones = set()  # 用于跟踪已处理的手机号
        for index, row in df.iterrows():
            # 创建新客户记录
            customer_data = {}
            for db_column in column_mapping.values():
                customer_data[db_column] = row.get(db_column)

            # 检查手机号是否已存在
            phone = customer_data.get('phone')
            if phone:
                if phone in processed_phones:
                    logger.warning(f"跳过重复手机号: {phone}")
                    continue  # 跳过重复数据
                processed_phones.add(phone)
            customer_data["user_id"] = current_user.user_id
            customers_data.append(customer_data)

        # 批量导入数据
        success_count, fail_count, errors = cust_dao.bulk_create_customers(customers_data)

        return Result.success(
            data={
                "total": len(customers_data),
                "success": success_count,
                "failed": fail_count,
                "errors": errors
            },
            msg=f"客户导入完成: 成功 {success_count} 条，失败 {fail_count} 条"
)

    except FileNotFoundError:
        logger.error(f"Excel文件未找到: {file_path}")
        return Result.fail(msg="Excel文件未找到")
    except pd.errors.EmptyDataError:
        logger.error("Excel文件为空")
        return Result.fail(msg="Excel文件为空")
    except pd.errors.ParserError as e:
        logger.exception(f"解析Excel文件失败: {type(e).__name__}")
        return Result.fail(msg=f"解析Excel文件失败: {str(e)}")
    except Exception as e:
        logger.exception(f"导入客户失败: {type(e).__name__}")
        return Result.fail(msg=f"导入客户失败: {str(e)}")


async def create_customer_service(customer_data: dict, db: Session = None) -> Result:
    """创建客户，如果客户已存在则更新"""
    try:
        cust_dao = CustomersDAO(db=db)

        # 检查客户是否已存在（通过手机号判断）
        phone = customer_data.get('phone')
        existing_customer = None
        if phone:
            existing_customer = cust_dao.get_customer_by_phone(phone)

        if existing_customer:
            # 客户已存在，执行更新操作
            # 过滤掉不应该更新的字段
            update_data = {k: v for k, v in customer_data.items()
                           if k not in ['id', 'customer_id', 'created_at']}

            updated_customer = cust_dao.update_customer(existing_customer.customer_id, update_data)
            if not updated_customer:
                return Result.fail(msg="更新客户失败")

            return Result.success(data=updated_customer.to_dict(), msg="客户更新成功")
        else:
            # 客户不存在，执行创建操作
            db_customer = cust_dao.create_customer(customer_data)
            if not db_customer:
                return Result.fail(msg="创建客户失败")

            return Result.success(data=db_customer.to_dict(), msg="客户创建成功")

    except Exception as e:
        logger.exception(f"创建/更新客户失败: {type(e).__name__}")
        return Result.fail(msg=f"创建/更新客户失败: {str(e)}")


async def get_customer_by_id_service(customer_id: int, db:Session = None) -> Result:
    """根据ID获取客户"""
    try:
        cust_dao = CustomersDAO(db=db)
        customer = cust_dao.get_customer_by_id(customer_id)
        if not customer:
            return Result.fail(msg="客户未找到")

        return Result.success(data=customer.to_dict(),msg="获取客户成功")
    except Exception as e:
        logger.exception(f"获取客户失败: {type(e).__name__}")
        return Result.fail(msg=f"获取客户失败: {str(e)}")


async def get_all_customers(skip: int = 0, limit: int = 100, db: Session = None,
                            customer_name: str = None, phone: str = None, city: str = None,
                            source: str = None, in_call_task: int = None,
                            user_id: str = None) -> Result:
    """获取所有客户"""
    try:
        cust_dao = CustomersDAO(db=db)
        customers, total_count = cust_dao.get_customers(skip, limit, customer_name, phone, city, source,
                                                        in_call_task=in_call_task, user_id=user_id)

        customers_data = [customer.to_dict() for customer in customers]

        return Result.success(
           data={
                "customers": customers_data,
                "total": total_count,
                "skip": skip,
                "limit": limit
            },
            msg="获取客户列表成功"
        )
    except Exception as e:
        logger.exception(f"获取客户列表失败: {type(e).__name__}")
        return Result.fail(msg=f"获取客户列表失败: {str(e)}")


async def update_customer_service(customer_id: int, customer_data: dict, db: Session = None) -> Result:
    """更新客户"""
    try:
        cust_dao = CustomersDAO(db=db)
        # 先获取客户信息
        customer = cust_dao.get_customer_by_id(customer_id)
        if not customer:
            return Result.fail(msg="客户未找到")

        # 执行更新操作
        db_customer = cust_dao.update_customer(customer.customer_id, customer_data)
        if not db_customer:
            return Result.fail(msg="更新客户失败")

        return Result.success(data=db_customer.to_dict(), msg="客户更新成功")
    except Exception as e:
        logger.exception(f"更新客户失败: {type(e).__name__}")
        return Result.fail(msg=f"更新客户失败: {str(e)}")


async def delete_customer_service(customer_id: int, db: Session = None) -> Result:
    """删除客户"""
    try:
        cust_dao = CustomersDAO(db=db)
        success = cust_dao.delete_customer(customer_id)
        if not success:
            return Result.fail(msg="删除客户失败")

        return Result.success(msg="客户删除成功")
    except Exception as e:
        logger.exception(f"删除客户失败: {type(e).__name__}")
        return Result.fail(msg=f"删除客户失败: {str(e)}")


async def update_customer_platform_service(phone: str, platform: str, status: str,
                                           db: Session, added_time=None, request_time=None) -> Result:
    try:
        # 使用DAO层方法查询客户
        cust_dao = CustomersDAO(db=db)
        # 首先根据手机号查找客户
        customer = cust_dao.get_customer_by_phone(phone)
        if not customer:
            return Result.fail(msg=f"未找到手机号为 {phone} 的客户")

        # 检查客户在该平台是否已有记录
        existing_platform = cust_dao.get_customer_platform(customer.customer_id, platform)
        if existing_platform:
            # 如果记录存在，更新记录
            cust_dao.update_customer_platform(customer.customer_id, platform, status, added_time, request_time)
        else:
            customer_platform = cust_dao.create_customer_platform(customer.customer_id, platform, status)
        return Result.success(msg="更新成功")
    except Exception as e:
        db.rollback()
        logger.exception(f"更新客户平台信息时发生错误: {type(e).__name__}")
        return Result.fail(msg=f"更新客户平台信息失败: {str(e)}")


async def update_customer_situation_by_external_id(external_id: str, customer_situation: str,
                                                   db: Session = None) -> Result:
    try:
        cust_dao = CustomersDAO(db=db)

        customer = cust_dao.get_customer_by_external_id(external_id)

        if not customer:
            return Result.fail(msg=f"未找到ID为 {external_id} 的客户")

        # 更新客户情况
        update_data = {
            'customer_situation': customer_situation
        }

        updated_customer = cust_dao.update_customer(customer.customer_id, update_data)
        if not updated_customer:
            return Result.fail(msg="更新客户情况失败")

        return Result.success(data=updated_customer.to_dict(), msg="客户情况更新成功")

    except Exception as e:
        logger.exception(f"通过外部ID更新客户情况失败: {type(e).__name__}")
        return Result.fail(msg=f"更新客户情况失败: {str(e)}")


async def get_following_customers(skip: int = 0, limit: int = 100, db: Session = None,
                                  customer_name: str = None,phone: str = None, city: str = None,
                                  source: str = None) -> Result:
    """获取跟进客户列表"""
    try:
        cust_dao = CustomersDAO(db=db)
        customers, total_count = cust_dao.get_following_customers(skip, limit, customer_name, phone,city, source)

        customers_data = [customer.to_dict() for customer in customers]

        return Result.success(
            data={
                "customers": customers_data,
                "total": total_count,
                "skip": skip,
                "limit": limit
            },
            msg="获取跟进客户列表成功"
        )
    except Exception as e:
        logger.exception(f"获取跟进客户列表失败: {type(e).__name__}")
        return Result.fail(msg=f"获取跟进客户列表失败: {str(e)}")


async def batch_update_following_customers_service(customer_ids: List[str], db: Session = None) -> Result:
    """
    批量更新客户状态为"我跟进的客户"

    Args:
        customer_ids: 客户ID列表
        db: 数据库会话

    Returns:
        Result: 更新结果
    """
    try:
        cust_dao = CustomersDAO(db=db)

        #批量更新客户状态
        success_count = cust_dao.batch_update_following_customers(customer_ids)

        if success_count == len(customer_ids):
            return Result.success(msg=f"成功更新 {success_count} 位客户的跟进状态")
        elif success_count > 0:
            return Result.success(msg=f"部分更新成功，{success_count}/{len(customer_ids)} 位客户状态已更新")
        else:
            return Result.fail(msg="批量更新失败，没有客户被更新")

    except Exception as e:
        logger.exception(f"批量更新客户状态为'我跟进的客户'失败: {type(e).__name__}")
        return Result.fail(msg=f"批量更新失败: {str(e)}")


async def update_customer_callState_by_phone(phone: str, callState: int,
                                             db: Session = None) -> Result:
    try:
        cust_dao = CustomersDAO(db=db)
        customer = cust_dao.get_customer_by_phone(phone)

        if not customer:
            return Result.fail(msg=f"未找到手机号为 {phone} 的客户")

        # 更新客户情况
        update_data = {
            'call_status': callState
        }

        updated_customer = cust_dao.update_customer(customer.customer_id, update_data)
        if not updated_customer:
            return Result.fail(msg="更新客户情况失败")

        return Result.success(data=updated_customer.to_dict(), msg="客户情况更新成功")
    except Exception as e:
        logger.exception(msg=f"通过手机号更新客户通话状态失败: {str(e)}")
        return Result.fail(msg=f"更新客户通话状态失败: {str(e)}")


async def create_or_update_h5_customer(customer_data: dict, db: Session = None) -> Result:
    """
    H5端创建或更新客户信息

    Args:
        customer_data: 客户数据字典，必须包含phone字段
        db: 数据库会话

    Returns:
        Result: 操作结果
    """
    try:
        cust_dao = CustomersDAO(db=db)

        # 检查手机号是否提供
        phone = customer_data.get('phone')
        if not phone:
            return Result.fail(msg="手机号不能为空")

        # 根据手机号查找客户
        existing_customer = cust_dao.get_customer_by_phone(phone)
        customer_data['source'] = 'h5'

        if existing_customer:
            # 客户已存在，执行更新操作
            # 过滤掉不应该更新的字段
            update_data = {k: v for k, v in customer_data.items()
                           if k not in ['id', 'customer_id', 'created_at']}
            
            # 特殊处理 customer_situation 字段，实现累加而非覆盖
            if 'customer_situation' in update_data and update_data['customer_situation']:
                # 如果新数据中有 customer_situation 且不为空
                if existing_customer.customer_situation:
                    # 如果原数据中也有内容，则追加
                    update_data['customer_situation'] = existing_customer.customer_situation + "; " + update_data['customer_situation']
                # 如果原数据中无内容，则直接使用新数据

            updated_customer = cust_dao.update_customer(existing_customer.customer_id, update_data)
            if not updated_customer:
                return Result.fail(msg="更新客户失败")

            return Result.success(data=updated_customer.to_dict(), msg="客户信息更新成功")
        else:
            db_customer = cust_dao.create_customer(customer_data)
            if not db_customer:
                return Result.fail(msg="创建客户失败")

            return Result.success(data=db_customer.to_dict(), msg="客户创建成功")

    except Exception as e:
        logger.exception(msg=f"H5创建/更新客户失败")
        return Result.fail(msg=f"操作失败: {str(e)}")


async def batch_create_customers_service(customers_data: List[dict], current_user: Users, db: Session = None) -> Result:
    """
    批量创建客户

    Args:
customers_data: 客户数据列表
        current_user: 当前用户
        db: 数据库会话

    Returns:
        Result: 批量创建结果
    """
    try:
        cust_dao = CustomersDAO(db=db)

        # 为每个客户数据添加用户ID
        for customer_data in customers_data:
            customer_data["user_id"] = current_user.user_id

        # 批量创建客户
        success_customers, fail_count, errors = cust_dao.batch_create_customers(customers_data)

        success_count = len(success_customers)
        success_data = [customer.to_dict() for customer in success_customers]

        return Result.success(
            data={
                "success_customers": success_data,
                "success_count": success_count,
                "failed_count": fail_count,
                "errors": errors
            },
            msg=f"批量创建客户完成: 成功 {success_count}条，失败 {fail_count} 条"
        )
    except Exception as e:
        logger.exception(f"批量创建客户失败: {type(e).__name__}")
        return Result.fail(msg=f"批量创建客户失败: {str(e)}")


async def save_chat_message(message_data: dict, redis_service: AsyncRedisService) -> Result:
    """
    保存聊天记录到Redis

    Args:
        message_data: 聊天记录数据
        redis_service: Redis服务实例

    Returns:
        Result: 操作结果
    """
    try:
        # 获取必要字段
        session_id = message_data.get('session_id')
        sender = message_data.get('sender')
        receiver = message_data.get('receiver')
        content = message_data.get('content')

        # 验证必要字段
        if not all([session_id, sender, receiver, content]):
            return Result.fail(msg="缺少必要参数: session_id, sender, receiver, content")
        # 构建存储键
        key = f"chat_message:{session_id}"
        # 构建消息数据
        message = {
            "sender": sender,
            "receiver": receiver,
            "content": content,
            "timestamp": datetime.now().isoformat()
        }
        # 将消息添加到Redis列表中
        await redis_service.redis_service.lpush(key, json.dumps(message))
        # # 设置过期时间（例如：7天）
        # await redis_service.redis_service.expire(key, 60 * 60 * 24 * 7)
        return Result.success(msg="聊天记录保存成功")
    except Exception as e:
        logger.exception("保存聊天记录失败")
        return Result.fail(msg=f"保存聊天记录失败: {str(e)}")


async def get_chat_messages(session_id: str, redis_service: AsyncRedisService)-> Result:
    """
    从Redis获取聊天记录

    Args:
        session_id: 会话ID
        redis_service: Redis服务实例

    Returns:
        Result: 包含聊天记录列表的操作结果
    """
    try:
        # 构建存储键
        key = f"chat_message:{session_id}"

        # 从Redis获取所有消息
        messages = await redis_service.redis_service.lrange(key, 0, -1)

        # 解析消息数据
        parsed_messages = []
        for message_str in messages:
            try:
                message = json.loads(message_str)
                parsed_messages.append(message)
            except json.JSONDecodeError:
                logger.warning(f"无法解析消息: {message_str}")
                continue

        return Result.success(data=parsed_messages, msg="获取聊天记录成功")
    except Exception as e:
        logger.exception("获取聊天记录失败")
        return Result.fail(msg=f"获取聊天记录失败: {str(e)}")


async def update_customer_sv(customer_id: str, customer_data: dict, db: Session = None) -> Result:
    """根据customer_id更新客户信息"""
    try:
        cust_dao = CustomersDAO(db=db)

        # 检查客户是否存在
        customer = cust_dao.get_customer_by_customer_id(customer_id)
        if not customer:
            return Result.fail(msg=f"未找到ID为 {customer_id} 的客户")

        # 检查手机号是否已存在（如果正在更新手机号）
        if 'phone' in customer_data and customer_data['phone'] != customer.phone:
            existing_customer = cust_dao.get_customer_by_phone(customer_data['phone'])
            if existing_customer and existing_customer.customer_id != customer_id:
                return Result.fail(msg=f"手机号 {customer_data['phone']} 已被其他客户使用")

        # 过滤掉不应该更新的字段
        update_data = {k: v for k, v in customer_data.items()
                       if k not in ['id', 'customer_id', 'created_at']}

        # 执行更新操作
        updated_customer = cust_dao.update_customer(customer_id, update_data)
        if not updated_customer:
            return Result.fail(msg="更新客户失败")

        return Result.success(data=updated_customer.to_dict(), msg="客户更新成功")

    except Exception as e:
        logger.exception(f"更新客户失败: {type(e).__name__}")
        return Result.fail(msg=f"更新客户失败: {str(e)}")

async def run_task_service(customers_data: List[dict],
                            channel,
                            db: Session = None) -> Result:
        try:
            cust_dao = CustomersDAO(db=db)
            # 批量创建客户
            success_customers,existing_customers, fail_count, errors = cust_dao.batch_create_customers(customers_data)

            success_count = len(success_customers)
            success_data = [customer.to_dict() for customer in success_customers]
            customer_ids = [customer.customer_id for customer in success_customers]
            existing_customers_ids = [customer.customer_id for customer in existing_customers]
            customer_ids = customer_ids + existing_customers_ids
            if customer_ids:
                timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
                req = CallTasksCreateRequest(task_name=f'auto_{timestamp}',
                                             created_by='auto',
                                             channel=channel,
                                             customer_ids=customer_ids)
                call_service = get_call_task_service()
                current_user = Users(user_id="auto")
                responses = await call_service.create_call_task(req, db, current_user)
                await call_service.start_task(responses.task_id, db)
                task_ids = [responses.task_id]
                return Result.success(msg='ok',data=task_ids)
            else:
                return Result.fail(msg='任务创建失败，无可用客户')
        except Exception as e:
            logger.exception(f"批量创建客户失败: {type(e).__name__}")
            return Result.fail(msg=f"批量创建客户失败: {str(e)}")